package mwa.cs.dao;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.json.simple.JSONArray;

import mwa.cs.database.Database;
import mwa.cs.model.Ar;
import mwa.cs.util.Converter;

/**
 * A class used for managing AR
 */
public class ArDAO {

	/** a database of this AR */
	private Database db;
	Converter voConverter = new Converter();

	/**
	 * Instantiate a new object of this class
	 * @param database a database
	 */
	public ArDAO(Database db) {
		this.db = db;
	}

	public List<Map<String, Object>> findAll() {
		String vaSql = "SELECT * FROM ar WHERE ";
		List<Map<String, Object>> result = db.queryList(vaSql);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	public String findMaxMonth(int pnSiteId) {
		String vaSql = "SELECT MAX(period_month) AS period_month FROM ar WHERE period_year = (SELECT MAX(period_year) FROM ar) and site_id = ?";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId);
		if (result.get("PERIOD_MONTH") != null) {
			return result.get("PERIOD_MONTH").toString();
		} else {
			return "00";
		}
	}

	public String findMaxYear(int pnSiteId) {
		String vaSql = "SELECT MAX(period_year) AS period_year FROM ar WHERE site_id = ?";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId);
		if (result.get("PERIOD_YEAR") != null) {
			return result.get("PERIOD_YEAR").toString();
		} else {
			return "0000";
		}
	}

	public List<Map<String, Object>> findArDataByAccountCode(int pnSiteId,
			String paAccountCode) {
		String vaSql = "SELECT account, pname, fname, lname, customer_class, address, bill_number, account_receivable_date,"
				+ " water_charge, water_fee, vat_amount, bill_amount, garbage_charge, wastewater_charge, other_charge,"
				+ " total_amount FROM ar WHERE site_id = ? AND account = ? AND status is null AND receive_date is null";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				paAccountCode);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	public List<Map<String, Object>> findArDataByBillNumber(int vnSiteId,
			String vaBillNumber) {
		String vaSql = "SELECT account, pname, fname, lname, customer_class, address, bill_number, account_receivable_date,"
				+ " water_charge, water_fee, vat_amount, bill_amount, garbage_charge, wastewater_charge, other_charge,"
				+ " total_amount FROM ar WHERE site_id = ? AND bill_number = ? AND status is null AND receive_date is null";
		List<Map<String, Object>> result = db.queryList(vaSql, vnSiteId,
				vaBillNumber);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	public List<Map<String, Object>> findArDataByAccountCodeAndBillNumber(
			int vnSiteId, String paBillNumber, String paAccountCode) {
		String vaSql = "SELECT account, pname, fname, lname, customer_class, address, bill_number, account_receivable_date,"
				+ " water_charge, water_fee, vat_amount, bill_amount, garbage_charge, wastewater_charge, other_charge,"
				+ " total_amount FROM ar WHERE site_id = ? AND bill_number = ? AND account = ? AND status is null AND receive_date is null";
		List<Map<String, Object>> result = db.queryList(vaSql, vnSiteId,
				paBillNumber, paAccountCode);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	public void UpdateAr(int pnSiteId, int pnUserId, String paDate,
			String paTime, String paPayerName, String pnBillnumber,
			String paAccount, String paSerialNumber) {
		String vaSql = "Update ar SET receive_date = ? , receive_time = ? , "
				+ " print_serial_number = (SELECT (print_serial_number)+1 FROM ar WHERE site_id = ? AND account = ? AND bill_number = ? AND status is null) "
				+ ", pay_by = null , receive_employee = ? , serial_number = ? WHERE site_id = ? and account = ? AND bill_number = ? ";
		try {
			db.update(vaSql, paDate, paTime, pnSiteId, paAccount, pnBillnumber,
					pnUserId, paSerialNumber, pnSiteId, paAccount, pnBillnumber);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * count AR in this period_month and period_year
	 * @param pnSiteId site that will count
	 * @param paPeriodMonth period month that will count
	 * @param paPeriodYear period_year that will count
	 * @return counting number of AR
	 */
	public int CountArInPeriod(int pnSiteId, String paPeriodMonth,
			String paPeriodYear) {
		String vaSql = "SELECT count(*) FROM ar WHERE site_id = ? and period_month = ? and period_year = ?";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId,
				paPeriodMonth, paPeriodYear);
		if (result != null) {
			return Integer.parseInt(result.get("COUNT(*)").toString());
		} else {
			return 0;
		}
	}

	/**
	 * finding CustomerStatisticDataTable
	 * @param paAcount
	 * @return JSONArray CustomerStatisticDataTable
	 */
	public JSONArray getCustomerStatisticDataTable(int pnSiteId,
			String paAccount) {

		String vaSql = "SELECT ROWNUM AS rnum,read_date,current_read_number,consumption "
				+ "FROM(SELECT substr(current_read_date,7,2)||'/'|| substr(current_read_date,5,2) ||'/'||substr(current_read_date,1,4) "
				+ "AS read_date,current_read_number,consumption,current_read_date "
				+ "FROM ar WHERE site_id=? AND account = ? AND (status IS NULL OR status != 'C') ORDER BY current_read_date DESC) WHERE rownum <= 12";

		List<Map<String, Object>> result = db.queryListDataTable(vaSql, pnSiteId,
				paAccount);
		if (result != null) {
			return voConverter.convertListToJsonDataTable(result);
		} else {
			return null;
		}

	}

	/**
	 * finding CustomerStatisticGrap
	 * @param paAcount
	 * @return List<Map<String, Object>> CustomerStatisticGrap
	 */
	public List<Map<String, Object>> getCustomerStatisticGrap(int pnSiteId,
			String paAccount) {

		String vaSql = "SELECT ROWNUM AS rnum,read_date,current_read_number,consumption,current_read_date "
				+ "FROM(SELECT substr(current_read_date,5,2) ||'/'||substr(current_read_date,1,4) "
				+ "AS read_date,current_read_number,consumption, current_read_date "
				+ "FROM ar WHERE site_id=? AND account = ? AND (status IS NULL OR status != 'C') ORDER BY current_read_date DESC) WHERE rownum <= 12";

		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				paAccount);
		if (result != null) {
			return result;
		} else {
			return null;
		}

	}

	public void insertAr(Ar poAr) {
		String vaSql = "INSERT INTO ar( site_id,zone_id,route_id,account,period_month,period_year,seq,customer_class,"
				+ "pname,fname,lname,address,serial_number,bill_number,bill_type,consumption,allowance,water_fee,"
				+ "water_charge,vat_amount,bill_amount,previous_read_date,previous_read_number,current_read_date,"
				+ "current_read_number,account_receivable_date,account_receivable_time,status,void_date,void_time,"
				+ "void_employee,receive_date,receive_time,receive_employee,pay_by,print_bill_number,print_serial_number,"
				+ "total_amount,reason,wastewater_charge,garbage_charge,other_charge,insert_user_id,insert_date,insert_time,"
				+ "update_user_id,update_date,update_time) "
				+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			db.insert(vaSql, poAr.getVnSiteId(), poAr.getVnZoneId(),
					poAr.getVnRouteId(), poAr.getVaAccount(),
					poAr.getVaPeriodMonth(), poAr.getVaPeriodYear(),
					poAr.getVnSeq(), poAr.getVaCustomerClas(),
					poAr.getVaPName(), poAr.getVaFName(), poAr.getVaLName(),
					poAr.getVaAddress(), poAr.getVnSerialNumber(),
					poAr.getVaBillNumber(), poAr.getVnBillType(),
					poAr.getVnConsumption(), poAr.getVaAllowance(),
					poAr.getVnWaterFee(), poAr.getVnWaterCharge(),
					poAr.getVnVatAmount(), poAr.getVnBillAmount(),
					poAr.getVaPreviousReadDate(),
					poAr.getVnPreviousReadNumber(),
					poAr.getVaCurrentReadDate(), poAr.getVnCurrentReadNumber(),
					poAr.getVaAccountReceivableDate(),
					poAr.getVaAccountReceivableTime(), poAr.getVaStatus(),
					poAr.getVaViodDate(), poAr.getVaVoidTime(),
					poAr.getVnViodEmployee(), poAr.getVaReceiveDate(),
					poAr.getVaReceiveTime(), poAr.getVnReceiveEmployee(),
					poAr.getVaPayBy(), poAr.getVnPrintBillNumber(),
					poAr.getVnPrintSerialNumber(), poAr.getVnTotalAmount(),
					poAr.getVaReason(), poAr.getVnWasteWaterCharge(),
					poAr.getVnGarbageCharge(), poAr.getVnOtherCharge(),
					poAr.getVnInsertUserId(), poAr.getVaInsertDate(),
					poAr.getVaInsertTime(), poAr.getVnUpdateUserId(),
					poAr.getVaUpdateDate(), poAr.getVaUpdateTime()

			);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	// ค้นหาข้อมูล AR เพื่อแก้ไขการอ่านมาตร
	public List<Map<String, Object>> searchMeterAccount(int vaSiteId,
			String paAccount, String paPeriodMount, String paPeriodYear) {

		String vaSql = "select ar.bill_number, z.zone_name, r.route_description, ar.seq, ar.account, "
				+ " ar.pname || ' ' || ar.fname || ' ' || ar.lname as full_name, cs.description, ar.customer_class, ar.address, ar.allowance, "
				+ " substr(ar.previous_read_date,7,2) || '/' || substr(ar.previous_read_date,5,2) || '/' || substr(ar.previous_read_date,1,4) as previous_read_date, "
				+ " substr(ar.current_read_date,7,2) || '/' || substr(ar.current_read_date,5,2) || '/' || substr(ar.current_read_date,1,4) as current_read_date, "
				+ " ar.previous_read_number, ar.current_read_number, "
				+ " ar.consumption, ar.water_charge, ar.water_fee, ar.vat_amount, ar.bill_amount, ar.garbage_charge, "
				+ " ar.wastewater_charge, ar.other_charge, ar.total_amount, case ar.status when 'C' then '0' else '1' end as status, "
				+ " ar.receive_date, "
				+ " case ar.print_bill_number when 0 then '0' else '1' end as print_bill_number "
				+ " from ar ar "
				+ " inner join zone z on ar.zone_id = z.zone_id "
				+ " inner join route r on ar.route_id = r.route_id "
				+ " left join customer_class cs on ar.customer_class = cs.code "
				+ " where ar.site_id = ? and ar.zone_id = r.zone_id and ar.account = ? and ar.period_month = ? "
				+ " and ar.period_year = ? ";
		// + " and (ar.print_bill_number = 0 and ar.receive_date is null)";
		List<Map<String, Object>> result = db.queryList(vaSql, vaSiteId,
				paAccount, paPeriodMount, paPeriodYear);
		return result;
	}

	// แก้ไขข้อมูล AR การอ่านมาตร
	public boolean updateMeterAR(Ar poNewAr) {
		String vaSql = "UPDATE ar SET "
				+ "bill_type = ?, consumption = ?, water_fee = ?, water_charge = ?, vat_amount = ?, bill_amount = ?, "
				+ "previous_read_date = ?, previous_read_number = ?, current_read_date = ?, current_read_number = ?, "
				+ "total_amount = ?, garbage_charge = ?, wastewater_charge = ?, other_charge = ?, "
				+ "update_user_id = ?, update_date = ?, update_time = ? "
				+ "WHERE site_id = ? AND account = ? AND period_month = ? AND period_year = ?";

		boolean result = false;
		try {
			db.update(vaSql, poNewAr.getVnBillType(), poNewAr.getVnConsumption(),
					poNewAr.getVnWaterFee(), poNewAr.getVnWaterCharge(),
					poNewAr.getVnVatAmount(), poNewAr.getVnBillAmount(),
					poNewAr.getVaPreviousReadDate(),
					poNewAr.getVnPreviousReadNumber(),
					poNewAr.getVaCurrentReadDate(),
					poNewAr.getVnCurrentReadNumber(),
					poNewAr.getVnTotalAmount(), poNewAr.getVnGarbageCharge(),
					poNewAr.getVnWasteWaterCharge(),
					poNewAr.getVnOtherCharge(), poNewAr.getVnUpdateUserId(),
					poNewAr.getVaUpdateDate(), poNewAr.getVaUpdateTime(),
					poNewAr.getVnSiteId(), poNewAr.getVaAccount(),
					poNewAr.getVaPeriodMonth(), poNewAr.getVaPeriodYear());
			result = true;
		} catch (Exception e) {
			System.out.println(e);
			result = false;
		}
		return result;
	}

	// ค้นหาข้อมูล AR เพื่อบันทึกใบแจ้งหนี้
	public List<Map<String, Object>> searchARForSave(int pnSiteId,
			String paAccount, String paPeriodMount, String paPeriodYear) {

		String vaSql = "select ar.bill_number, z.zone_name, r.route_description, ar.seq, ar.account, "
				+ " ar.pname || ' ' || ar.fname || ' ' || ar.lname as full_name, cs.description, ar.customer_class, ar.address, ar.allowance "
				+ " from ar ar "
				+ " inner join zone z on ar.zone_id = z.zone_id "
				+ " inner join route r on ar.route_id = r.route_id "
				+ " left join customer_class cs on ar.customer_class = cs.code "
				+ " where ar.site_id = ? and ar.zone_id = r.zone_id and ar.account = ? and ar.period_month = ? "
				+ " and ar.period_year = ? and ar.status = 'C'";
		// + " and (ar.print_bill_number = 0 and ar.receive_date is null)";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				paAccount, paPeriodMount, paPeriodYear);

		return result;
	}

	// นับจำนวน AR
	public int CountArForSave(int pnSiteId, String paAccount,
			String paPeriodMonth, String paPeriodYear) {
		String vaSql = "SELECT count(*) FROM ar WHERE site_id = ? AND account = ? AND period_month = ? AND period_year = ?";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paAccount,
				paPeriodMonth, paPeriodYear);
		if (result != null) {
			return Integer.parseInt(result.get("COUNT(*)").toString());
		} else {
			return 0;
		}
	}

	// บันทึกข้อมูล AR ใบแจ้งหนี้ใหม่
	public boolean SaveNewAR(int pnSiteId, String paAccount, String paPeriodMonth,
			String paPeriodYear, Ar poNewAr) {
		String vaSql = "INSERT INTO ar ( "
				+ "site_id, zone_id, route_id, account, period_month, period_year, seq, customer_class, pname, fname, lname, address, "
				+ "serial_number, bill_number, bill_type, consumption, allowance, water_fee, water_charge, vat_amount, bill_amount, "
				+ "previous_read_date, previous_read_number, current_read_date, current_read_number, account_receivable_date, account_receivable_time, "
				+ "void_employee, receive_employee, print_bill_number, print_serial_number, total_amount, reason, wastewater_charge, garbage_charge,  "
				+ "other_charge, insert_user_id, insert_date, insert_time, update_user_id) "
				+ "SELECT DISTINCT site_id, zone_id, route_id, ?, ?, ?, seq, customer_class, pname, fname, lname, address, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
				+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? "
				+ "FROM ar WHERE site_id = ? AND account = ? AND period_month = ? AND period_year = ?";
		boolean result = false;
		try {
			db.update(vaSql, paAccount, paPeriodMonth, paPeriodYear,
					poNewAr.getVnSerialNumber(), poNewAr.getVaBillNumber(),
					poNewAr.getVnBillType(), poNewAr.getVnConsumption(),
					poNewAr.getVaAllowance(), poNewAr.getVnWaterFee(),
					poNewAr.getVnWaterCharge(), poNewAr.getVnVatAmount(),
					poNewAr.getVnBillAmount(), poNewAr.getVaPreviousReadDate(),
					poNewAr.getVnPreviousReadNumber(),
					poNewAr.getVaCurrentReadDate(),
					poNewAr.getVnCurrentReadNumber(),
					poNewAr.getVaAccountReceivableDate(),
					poNewAr.getVaAccountReceivableTime(),
					poNewAr.getVnViodEmployee(),
					poNewAr.getVnReceiveEmployee(),
					poNewAr.getVnPrintBillNumber(),
					poNewAr.getVnPrintSerialNumber(),
					poNewAr.getVnTotalAmount(), poNewAr.getVaReason(),
					poNewAr.getVnWasteWaterCharge(),
					poNewAr.getVnGarbageCharge(), poNewAr.getVnOtherCharge(),
					poNewAr.getVnInsertUserId(), poNewAr.getVaInsertDate(),
					poNewAr.getVaInsertTime(), poNewAr.getVnUpdateUserId(),
					pnSiteId, paAccount, paPeriodMonth, paPeriodYear);
			result = true;
		} catch (Exception e) {
			System.out.println(e);
			result = false;
		}
		return result;
	}

	// ค้นหาข้อมูล AR เพื่อแก้ไขใบแจ้งหนี้
	public List<Map<String, Object>> searchARForUpdate(int pnSiteId,
			String paAccount, String paPeriodMount, String paPeriodYear) {

		String vaSql = "select ar.account, z.zone_name, r.route_description, ar.seq, ar.pname || ' ' || ar.fname || ' ' || ar.lname as full_name, "
				+ " cs.description, ar.customer_class, ar.address, ar.allowance, "
				+ " substr(ar.previous_read_date,7,2) || '/' || substr(ar.previous_read_date,5,2) || '/' || substr(ar.previous_read_date,1,4) as previous_read_date, "
				+ " substr(ar.current_read_date,7,2) || '/' || substr(ar.current_read_date,5,2) || '/' || substr(ar.current_read_date,1,4) as current_read_date, "
				+ " ar.previous_read_number, ar.current_read_number, ar.reason, "
				+ " ar.consumption, ar.water_charge, ar.water_fee, ar.vat_amount, ar.bill_amount, ar.garbage_charge, "
				+ " ar.wastewater_charge, ar.other_charge, ar.total_amount, case ar.status when 'C' then '0' else '1' end as status, "
				+ " ar.receive_date "
				+ " from ar ar "
				+ " inner join zone z on ar.zone_id = z.zone_id "
				+ " inner join route r on ar.route_id = r.route_id "
				+ " left join customer_class cs on ar.customer_class = cs.code "
				+ " where ar.site_id = ? and ar.zone_id = r.zone_id and ar.account = ? and ar.period_month = ? "
				+ " and ar.period_year = ? order by ar.insert_date desc, ar.insert_time desc";
		// + " and (ar.print_bill_number = 0 and ar.receive_date is null)";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				paAccount, paPeriodMount, paPeriodYear);

		return result;
	}

	// ค้นหาข้อมูล จำนวน AR เพื่อแก้ไขใบแจ้งหนี้
	public Map<String, Object> searchCountAR(int pnSiteId, String paAccount,
			String paPeriodMount, String paPeriodYear) {
		String vaSql = "select count(*) as count from ar "
				+ " where site_id = ? and account = ? and period_month = ? "
				+ " and period_year = ? and status = 'C'";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paAccount,
				paPeriodMount, paPeriodYear);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	// แก้ไขข้อมูล AR ใบแจ้งหนี้ใหม่
	public boolean editNewAR(int pnSiteId, String paAccount, String paPeriodMonth,
			String paPeriodYear, Ar poNewAr) {
		String vaSql = "UPDATE ar SET "
				+ "consumption = ?, water_fee = ?, water_charge = ?, vat_amount = ?, bill_amount = ?, "
				+ "current_read_date = ?, current_read_number = ?, garbage_charge = ?, wastewater_charge = ?, other_charge = ?, "
				+ "total_amount = ?, reason = ?, update_user_id = ?, update_date = ?, update_time = ? "
				+ "WHERE site_id = ? AND account = ? AND period_month = ? AND period_year = ? AND status IS null";

		boolean result = false;
		try {
			db.update(vaSql, poNewAr.getVnConsumption(), poNewAr.getVnWaterFee(),
					poNewAr.getVnWaterCharge(), poNewAr.getVnVatAmount(),
					poNewAr.getVnBillAmount(), poNewAr.getVaCurrentReadDate(),
					poNewAr.getVnCurrentReadNumber(),
					poNewAr.getVnGarbageCharge(),
					poNewAr.getVnWasteWaterCharge(),
					poNewAr.getVnOtherCharge(), poNewAr.getVnTotalAmount(),
					poNewAr.getVaReason(), poNewAr.getVnUpdateUserId(),
					poNewAr.getVaUpdateDate(), poNewAr.getVaUpdateTime(),
					pnSiteId, paAccount, paPeriodMonth, paPeriodYear);
			result = true;
		} catch (Exception e) {
			System.out.println(e);
			result = false;
		}
		return result;
	}

	// ค้นหาข้อมูล AR เพื่อยกเลิกใบแจ้งหนี้
	public Map<String, Object> searchARForCancel(int pnSiteId, String paBillNumber) {
		String vaSql = "select ar.account, z.zone_name, r.route_description, ar.seq, "
				+ " ar.pname || ' ' || ar.fname || ' ' || ar.lname as full_name, cs.description, ar.address, ar.bill_number, "
				+ " substr(ar.account_receivable_date,7,2) || '/' || substr(ar.account_receivable_date,5,2) || '/' || substr(ar.account_receivable_date,1,4) as account_receivable_date, "
				+ " case ar.status when 'C' then '0' else '1' end as status, ar.receive_date, ar.consumption, "
				+ " ar.water_charge, ar.water_fee, ar.vat_amount, ar.bill_amount, ar.garbage_charge, "
				+ " ar.wastewater_charge, ar.other_charge, ar.total_amount "
				+ " from ar ar "
				+ " inner join zone z on ar.zone_id = z.zone_id "
				+ " inner join route r on ar.route_id = r.route_id "
				+ " left join customer_class cs on ar.customer_class = cs.code "
				+ " where ar.site_id = ? and ar.zone_id = r.zone_id and ar.bill_number = ?";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paBillNumber);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	// ยกเลิกข้อมูล AR ใบแจ้งหนี้
	public boolean cancelAR(String paBillNumber, Ar poNewAr) {
		String vaSql = "UPDATE ar SET "
				+ "status = ?, void_date = ?, void_time = ?, void_employee = ?, reason = ?, "
				+ "update_user_id = ?, update_date = ?, update_time = ? WHERE bill_number = ?";

		boolean result = false;
		try {
			db.update(vaSql, poNewAr.getVaStatus(), poNewAr.getVaViodDate(),
					poNewAr.getVaVoidTime(), poNewAr.getVnViodEmployee(),
					poNewAr.getVaReason(), poNewAr.getVnUpdateUserId(),
					poNewAr.getVaUpdateDate(), poNewAr.getVaUpdateTime(),
					paBillNumber);
			result = true;
		} catch (Exception e) {
			System.out.println(e);
			result = false;
		}
		return result;
	}

	// แก้ไขข้อมูล Ar เพื่อแก้ไขผลการยกเลิกใบเสร็จรับเงิน
	public boolean resetArForCancelReceive(Ar poNewAr, String paSerialNumber) {
		String vaSql = "UPDATE ar SET "
				+ "receive_date = ?, receive_time = ?, receive_employee = ?, update_user_id = ?, update_date = ?, update_time = ? "
				+ "WHERE site_id = ? AND serial_number = ?";
		boolean result = false;
		try {
			db.update(vaSql, poNewAr.getVaReceiveDate(),
					poNewAr.getVaReceiveTime(), poNewAr.getVnReceiveEmployee(),
					poNewAr.getVnUpdateUserId(), poNewAr.getVaUpdateDate(),
					poNewAr.getVaUpdateTime(), poNewAr.getVnSiteId(),
         			Long.parseLong(paSerialNumber));
			result = true;
		} catch (Exception e) {
			System.out.println(e);
			result = false;
		}
		return result;
	}

	/**
	 * @athor CeoFreealife get data AR find All by site
	 * @param pnSiteId
	 * @return List<Map<String , Object>> object
	 */
	public List<Map<String, Object>> findArBySite(int pnSiteId) {
		// String vaSql =
		// "SELECT bill_number,water_charge,pname,vat_amount,bill_amount FROM ar WHERE site_id = ? ";
		String vaSql = "SELECT * FROM AR WHERE site_id = ?";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	/**
	 * @athor CeoFreealife get data AR find All by site
	 * @param pnSiteId
	 * @return List<Map<String , Object>> object
	 */
	public List<Map<String, Object>> findArByCondition(int pnSiteId,
			int pnZoneId, int pnRouted, String paMonth, String paYear) {
		String vaSql = "SELECT * FROM AR WHERE "
				+ " SITE_ID = ? AND ZONE_ID = ?  AND ROUTE_ID  = ? AND "
				+ " PERIOD_MONTH = ? AND PERIOD_YEAR = ? AND STATUS IS NULL ";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				pnZoneId, pnRouted, paMonth, paYear);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	/**
	 * @desc this is update receive transaction group
	 * @Requeriment Condition site_id , route_id , bill_number , period_month ,
	 *              period_year
	 * @update receive_date , receive_time , employee
	 * @aother CEO Freealife
	 * @return
	 */
	public boolean updateRecriveBillNumber(Ar poAr) {
		boolean act = false;
		try {

			System.out.println("1 >> " + poAr.getVaReceiveDate());
			System.out.println("2 >> " + poAr.getVaReceiveTime());
			System.out.println("3 >> " + poAr.getVnReceiveEmployee());
			System.out.println("4 >> " + poAr.getVnUpdateUserId());
			System.out.println("5 >> " + poAr.getVaUpdateDate());
			System.out.println("6 >> " + poAr.getVaUpdateTime());
			System.out.println("7 >> " + poAr.getVnSiteId());
			System.out.println("8 >> " + poAr.getVnRouteId());
			System.out.println("9 >> " + poAr.getVaBillNumber());
			System.out.println("10 >> " + poAr.getVaPeriodMonth());
			System.out.println("11 >> " + poAr.getVaPeriodYear());

			String vaSql = "UPDATE AR SET RECEIVE_DATE = ? , "
					+ " RECEIVE_TIME  =  ? ,"
					+ " RECEIVE_EMPLOYEE = ? , "
					+ " UPDATE_USER_ID = ? , "
					+ " UPDATE_DATE  = ? ,"
					+ " UPDATE_TIME = ? "
					+ " WHERE SITE_ID = ? AND ROUTE_ID = ? AND BILL_NUMBER = ? AND PERIOD_MONTH = ? AND PERIOD_YEAR = ? ";

			db.update(vaSql, poAr.getVaReceiveDate(), poAr.getVaReceiveTime(),
					poAr.getVnReceiveEmployee(), poAr.getVnUpdateUserId(),
					poAr.getVaUpdateDate(), poAr.getVaUpdateTime(),
					poAr.getVnSiteId(), poAr.getVnRouteId(), poAr.getVaBillNumber(),
					poAr.getVaPeriodMonth(),poAr.getVaPeriodYear());

			act = true;

			System.out.println(" ArDAO Error status : " + act);

		} catch (Exception ex) {
			System.out.println(" ArDAO Error : " + ex);
			ex.printStackTrace();
			act = false;
		}
		return act;
	}

	/**
	 * @athor CeoFreealife get data AR find All by site
	 * @param pnSiteId
	 * @return List<Map<String , Object>> object
	 */
	public List<Map<String, Object>> findArByCondition(int pnSiteId,
			int pnZoneId, int pnRouted, String paMonth, String paYear, String paBillNo) {
		String vaSql = "SELECT * FROM AR WHERE "
				+ " SITE_ID = ? AND ZONE_ID = ?  AND ROUTE_ID  = ? AND "
				+ " PERIOD_MONTH = ? AND PERIOD_YEAR = ? AND  BILL_NUMBER = ? AND STATUS IS NULL ";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				pnZoneId, pnRouted, paMonth, paYear, paBillNo);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	/**
	 * @athor CeoFreealife get data AR find All by site
	 * @param pnSiteId
	 * @return List<Map<String , Object>> object
	 */
	public Map<String, Object> findAR(int pnSiteId, int pnZoneId, int pnRouted,
			String paMonth, String paYear, String paBillNo) {
		String vaSql = "SELECT * FROM AR WHERE "
				+ " SITE_ID = ? AND ZONE_ID = ?  AND ROUTE_ID  = ? AND "
				+ " PERIOD_MONTH = ? AND PERIOD_YEAR = ? AND  BILL_NUMBER = ? AND RECEIVE_DATE IS NULL AND RECEIVE_TIME IS NULL ";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId,
				pnRouted, paMonth, paYear, paBillNo);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	/**
	 * @athor CeoFreealife get data AR find All by site
	 * @param pnSiteId
	 * @return List<Map<String , Object>> object
	 */
	public List<Map<String, Object>> findArByConditionAndStart(int pnSiteId,
			int pnZoneId, int pnRouted, String paMonth, String paYear,
			int paStartPage, int pnEndPages) {
		String vaSql = " SELECT * FROM ( "
				+ " SELECT ROWNUM AS AR_C ,  AR.* FROM AR WHERE "
				+ " SITE_ID = ? AND ZONE_ID = ?  AND ROUTE_ID  = ? AND STATUS IS NULL AND "
				+ " PERIOD_MONTH = ? AND PERIOD_YEAR = ?" + " AND ROWNUM <= ? "
				+ " ) WHERE AR_C > ? ";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				pnZoneId, pnRouted, paMonth, paYear, pnEndPages, paStartPage);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	/**
	 * @athor CeoFreealife get data AR find All by site
	 * @param pnSiteId
	 * @return List<Map<String , Object>> object
	 */
	public List<Map<String, Object>> findArCountByCondition(int pnSiteId,
			int pnZoneId, int pnRouted, String paMonth, String paYear) {
		String vaSql = "SELECT COUNT(*) AS AR_COUNT FROM AR WHERE "
				+ " SITE_ID = ? AND ZONE_ID = ?  AND ROUTE_ID  = ? AND "
				+ " PERIOD_MONTH = ? AND PERIOD_YEAR = ?";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				pnZoneId, pnRouted, paMonth, paYear);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	public int getCountAllArByAccount(int pnSiteId, String paAccount,
			String paStartDate, String paToDate) {
		String vaSql = "SELECT COUNT(*) " + "FROM ar ar " + "WHERE ar.site_id = "
				+ pnSiteId;
		if (!paAccount.equals("")) {
			vaSql += " AND ar.account = '" + paAccount + "'";
		}
		if (!paStartDate.equals("")) {
			vaSql += " AND ar.current_read_date >= '" + paStartDate + "'";
		}
		if (!paToDate.equals("")) {
			vaSql += " AND ar.current_read_date <= '" + paToDate + "'";
		}
		vaSql += " AND (ar.status IS NULL OR ar.status != 'C')";
		Map<String, Object> result = db.querySingle(vaSql);
		if (result != null) {
			return Integer.parseInt(result.get("COUNT(*)").toString());
		} else {
			return 0;
		}
	}

	public List<Map<String, Object>> findArByAccount(int pnSiteId,
			String paAccount, String paStartDate, String paToDate,
			String paSortColumn, int pnStart, int pnEnd) {
		String vaSql = "SELECT cm.meter_serial_number, ar.current_read_number, ar.current_read_date, ar.consumption, ar.water_charge, "
				+ "ar.water_fee, ar.vat_amount, ar.total_amount "
				+ "FROM ar ar "
				+ "INNER JOIN customer cm ON ar.account = cm.account "
				+ "WHERE ar.site_id = " + pnSiteId;
		if (!paAccount.equals("")) {
			vaSql += " AND ar.account = '" + paAccount + "'";
		}
		if (!paStartDate.equals("")) {
			vaSql += " AND ar.current_read_date >= '" + paStartDate + "'";
		}
		if (!paToDate.equals("")) {
			vaSql += " AND ar.current_read_date <= '" + paToDate + "'";
		}
		vaSql += " AND (ar.status IS NULL OR ar.status != 'C')"
				+ " AND ROWNUM <= " + pnEnd + " AND ROWNUM > " + pnStart + ""
				+ " ORDER BY " + paSortColumn;
		List<Map<String, Object>> result = db.queryList(vaSql);
		return result;
	}

	public List<Map<String, Object>> findAllArByAccount(int pnSiteId,
			String paAccount, String paStartDate, String paToDate,
			String paSortColumn) {
		String vaSql = "SELECT cm.meter_serial_number, ar.current_read_number, ar.current_read_date, ar.consumption, ar.water_charge, "
				+ "ar.water_fee, ar.vat_amount, ar.total_amount "
				+ "FROM ar ar "
				+ "INNER JOIN customer cm ON ar.account = cm.account "
				+ "WHERE ar.site_id = " + pnSiteId;
		if (!paAccount.equals("")) {
			vaSql += " AND ar.account = '" + paAccount + "'";
		}
		if (!paStartDate.equals("")) {
			vaSql += " AND ar.current_read_date >= '" + paStartDate + "'";
		}
		if (!paToDate.equals("")) {
			vaSql += " AND ar.current_read_date <= '" + paToDate + "'";
		}
		vaSql += " AND (ar.status IS NULL OR ar.status != 'C')" + " ORDER BY "
				+ paSortColumn;
		List<Map<String, Object>> result = db.queryList(vaSql);
		return result;
	}

	/**
	 * @athor Cape get data AR find All by siteId and recieve date or employeeId or both
	 * @param pnSiteId,recieveDate,employeeId,startIndex,endIndex
	 * @return List<Map<String , Object>> object
	 */
	public List<Map<String, Object>> findArByConditionRecieveDateAndRecieveEmployee(
			int pnSiteId, String paRecieveDate, String paEmployeeId,
			int paStartIndex, int pnEndIndex) {

		String vaSql;

		if (pnEndIndex == -1) {
			vaSql = " " + "SELECT * " + "FROM AR ar " + "WHERE ar.SITE_ID = "
					+ pnSiteId + " ";
			if (!paRecieveDate.equals("")) {
				vaSql += "AND ar.RECEIVE_DATE = " + paRecieveDate + " ";
			} else {
				vaSql += "AND ar.RECEIVE_DATE IS NOT NULL ";
			}
			if (!paEmployeeId.equals("")) {
				vaSql += "AND ar.RECEIVE_EMPLOYEE = " + paEmployeeId + " ";
			}
			vaSql += "ORDER BY ar.INSERT_DATE ASC,ar.INSERT_TIME ASC ";

		} else {

			vaSql = " " + "SELECT ar.* " + "FROM ( " + "SELECT ROWNUM r,ar.* "
					+ "FROM " + "( " + "SELECT * " + "FROM AR ar "
					+ "WHERE ar.SITE_ID = " + pnSiteId + " ";
			if (!paRecieveDate.equals("")) {
				vaSql += "AND ar.RECEIVE_DATE = " + paRecieveDate + " ";
			} else {
				vaSql += "AND ar.RECEIVE_DATE IS NOT NULL ";
			}
			if (!paEmployeeId.equals("")) {
				vaSql += "AND ar.RECEIVE_EMPLOYEE = " + paEmployeeId + " ";
			}

			String endvaSql = " "
					+ "ORDER BY ar.INSERT_DATE ASC,ar.INSERT_TIME ASC "
					+ ") ar " + "WHERE ROWNUM <= " + pnEndIndex + " " + ") ar "
					+ "WHERE r >= " + paStartIndex + " ";

			vaSql += endvaSql;

		}

		List<Map<String, Object>> result = db.queryList(vaSql);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	/**
	 * @athor Cape get count data AR find All by siteId and recieve date or employeeId or both
	 * @param pnSiteId,recieveDate,employeeId
	 * @return List<Map<String , Object>> object
	 */
	public Map<String, Object> countFindArByConditionRecieveDateAndRecieveEmployee(
			int pnSiteId, String paRecieveDate, String paEmployeeId) {

		String vaSql = " " + "SELECT COUNT(*) " + "FROM AR ar "
				+ "WHERE ar.SITE_ID = " + pnSiteId + " ";
		if (!paRecieveDate.equals("")) {
			vaSql += "AND ar.RECEIVE_DATE = " + paRecieveDate + " ";
		} else {
			vaSql += "AND ar.RECEIVE_DATE IS NOT NULL ";
		}
		if (!paEmployeeId.equals("")) {
			vaSql += "AND ar.RECEIVE_EMPLOYEE = " + paEmployeeId + " ";
		}

		Map<String, Object> result = db.querySingle(vaSql);
		if (result != null) {
			return result;
		} else {
			return null;
		}

	}

	/**
	 * @athor Cape UPdate Receive_date and Recieve_time to null and Receive_id to 0
	 * @param userId, siteId,billNumber
	 * @return boolean
	 */
	public boolean updateCancelBill(int pnUserId, int pnSiteId, String paBillNumber) {
		boolean act = false;
		try {

			DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
			DateFormat timeFormat = new SimpleDateFormat("HHmmss");
			Date d = new Date();

			String vaSql = "UPDATE AR SET " + " RECEIVE_DATE = NULL , "
					+ " RECEIVE_TIME  =  NULL ," + " RECEIVE_EMPLOYEE = 0 , "
					+ " UPDATE_USER_ID = " + pnUserId + " , "
					+ " UPDATE_DATE  = " + dateFormat.format(d) + " ,"
					+ " UPDATE_TIME = " + timeFormat.format(d) + " "
					+ " WHERE SITE_ID = " + pnSiteId + " "
					+ " AND BILL_NUMBER = " + paBillNumber + " ";

			db.update(vaSql);
			act = true;

			System.out.println(" ArDAO Error status : " + act);

		} catch (Exception ex) {
			System.out.println(" ArDAO Error : " + ex);
			ex.printStackTrace();
			act = false;
		}
		return act;
	}

	/**
	 * @athor Cape get serial number by use siteId and sbillNumber to find
	 * @param pnSiteId,billNumber
	 * @return Map<String , Object>
	 */
	public Map<String, Object> getSerialNumberByBillNumber(int pnSiteId,
			String paBillNumber) {

		String vaSql = " " + "SELECT ar.SERIAL_NUMBER " + "FROM Ar ar "
				+ "WHERE ar.SITE_ID = '" + pnSiteId + "' "
				+ "AND ar.BILL_NUMBER = '" + paBillNumber + "' ";

		Map<String, Object> result = db.querySingle(vaSql);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

}
